package com.dfrz.stockMarket.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.catalina.authenticator.SavedRequest;

import com.dfrz.stockMarket.bean.MessageBean;
import com.dfrz.stockMarket.cons.Constants;
import com.dfrz.stockMarket.model.UserStocksModel;

public class UserStocksDao extends BaseDao {
/*	public static void main(String[] args) {
		try {
			System.out.println(getAllUserStockCodeById(44).getMsg());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}*/
	public static MessageBean getUserStocksByID(int ID) throws Exception {
		System.out.println("getUserStocksByID(int ID)-id="+ID);
		List<UserStocksModel> list = new ArrayList<>();
		//加载驱动
				Class.forName("oracle.jdbc.driver.OracleDriver");
				System.out.println("加载成功");
				//获取连接
				Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "hr", "hr");
				System.out.println("连接成功");
				//�?��事务
				conn.setAutoCommit(false);
				
				//获取stmt
				PreparedStatement stmt = conn.prepareStatement(" select userid, stockcode, stockname, costprice from sto_userstocks where userid=?");
				stmt.setInt(1, ID);
				ResultSet rs = stmt.executeQuery();
				while(rs.next()){
					UserStocksModel userStocksModel = new UserStocksModel();
					int userid = rs.getInt("userid");
					int stockcode = rs.getInt("stockcode");
					String stockname = rs.getString("stockname");
					int costprice = rs.getInt("costprice");
					System.out.println("stockname--"+stockname);
					
					userStocksModel.setCostPrice(costprice);
					userStocksModel.setStockCode(stockcode);
					userStocksModel.setStockName(stockname);
					userStocksModel.setUserID(userid);
					
					list.add(userStocksModel);
					
				}
				

				
				//提交or回滚
//				conn.rollback();
				//释放stmt conn
				rs.close();
				stmt.close();
				conn.close();
				System.out.println(list.toString());
				return new MessageBean(true, list);
	}
	public static MessageBean save(UserStocksModel userStocksModel) throws Exception {
		
		//加载驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		System.out.println("加载成功");
		//获取连接
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "hr", "hr");
		System.out.println("连接成功");
		//�?��事务
		conn.setAutoCommit(false);
		
			PreparedStatement stmt = conn.prepareStatement("insert into sto_userstocks(userid, stockcode, stockname, costprice)values(?, ?, ?, ?)");
			stmt.setInt(1, userStocksModel.getUserID());
			stmt.setString(3, userStocksModel.getStockName());
			stmt.setDouble(4, userStocksModel.getCostPrice());
			stmt.setInt(2, userStocksModel.getStockCode());

			int ret =stmt.executeUpdate();
			System.out.println(ret+"条语句被执行");
			stmt.close();
			
			conn.commit();
			
			conn.close();
		
			return new MessageBean(true, Constants.USER_SUCCESS_TYPE1);
		
	}
	public static MessageBean getAllUserStockCodeById(int ID) throws Exception {
		System.out.println("getUserStocksByID(int ID)-id="+ID);
		List<UserStocksModel> list = new ArrayList<>();
		//加载驱动
				Class.forName("oracle.jdbc.driver.OracleDriver");
				System.out.println("加载成功");
				//获取连接
				Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "hr", "hr");
				System.out.println("连接成功");
				//�?��事务
				conn.setAutoCommit(false);
				
				//获取stmt
				PreparedStatement stmt = conn.prepareStatement(" select stockcode from sto_userstocks where userid=?");
				stmt.setInt(1, ID);
				ResultSet rs = stmt.executeQuery();
				while(rs.next()){
					UserStocksModel userStocksModel = new UserStocksModel();
					int stockcode = rs.getInt("stockcode");
					
					userStocksModel.setStockCode(stockcode);
					
					list.add(userStocksModel);
					
				}
				

				
				//提交or回滚
//				conn.rollback();
				//释放stmt conn
				rs.close();
				stmt.close();
				conn.close();
				return new MessageBean(true, list);
	}
	
}
